package main;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import model.*;

import java.util.*;

public class Test {

	public static void main(String[] args) {

		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory("Day4JPA");
		EntityManager em = emf.createEntityManager();

		em.getTransaction().begin();

		List<Student> studenti = em.createQuery("select m from Student m")
				.getResultList();
		/*
		 * List<Materie> materi = em.createQuery("select m from Materie m")
		 * .getResultList(); List<Student> studenti =
		 * em.createQuery("select m from Student m") .getResultList();
		 * List<Nota> note = em.createQuery("select m from Nota m")
		 * .getResultList();
		 */
		em.getTransaction().commit();

		System.out.println("Notele obtinute de Sergiu la tehnologii oo");

		List<Nota> list = em
				.createQuery(
						"SELECT m from Nota m WHERE m.student.nume= :nume and m.materie.denumire = 'tehnologii oo'")
				.setParameter("nume", "Sergiu").getResultList();

		for (Nota temp : list) {
			System.out.println(temp.getNota() + "," + temp.getData());
		}

		/*
		 * for (Nota temp2 : note) { if
		 * (temp2.getStudent().getNume().equals("Sergiu")) { if
		 * (temp2.getMaterie().getDenumire().equals("tehnologii oo"))
		 * System.out.println(temp2); } }
		 */

		System.out
				.println("Numele studentilor cu nr maxim de rest la aceeasi materie");

		List<Materie> materie = em.createQuery("Select m from Materie m")
				.getResultList();

		for (Materie temp : materie) {

			List<Nota> note = em
					.createQuery(
							"Select n from Nota n where n.nota<5 and n.materie = :mat")
					.setParameter("mat", temp).getResultList();

			double max = 0.0;
			for (Nota temp2 : note) {
				for (Student temp3 : studenti) {
					
				    Long tempMax = (Long) em
							.createQuery(
									"Select count(n.nota) from Nota n where n.student =:stud and n.materie = :mat")
							.setParameter("stud", temp3)
							.setParameter("mat", temp).getSingleResult();
					tempMax--;
					if(tempMax>max)
						max=tempMax;
				}
			
			}
			//System.out.println(max);
			//System.out.println(em.createQuery("Select count(n.nota) from Nota n where n.student);
			//List<Nota> studRau = em.createQuery("Select n from Not)
		}

		System.out
				.println("\nNumele studentiilor care au peste 5 la tehnologii oo");

		System.out
				.println(em
						.createQuery(
								"SELECT DISTINCT m.student.nume from Nota m WHERE m.nota >= 5 and m.materie.denumire = 'tehnologii oo'")
						.getResultList());

		/*
		 * for (Nota temp : note) { if
		 * (temp.getMaterie().getDenumire().equals("tehnologii oo") &&
		 * temp.getNota() >= 5) { System.out.println(temp.getStudent()); } }
		 */
		System.out
				.println("\nMedia aritmetica a notelor peste 5 ale studentului Sergiu");

		System.out
				.println(em
						.createQuery(
								"Select avg(m.nota) from Nota m where m.student.nume = 'Sergiu' and m.nota>=5")
						.getResultList());

		/*
		 * for (Nota temp : note) { if
		 * (temp.getStudent().getNume().equals("Sergiu") && temp.getNota() >= 5)
		 * { suma += temp.getNota(); counter++;
		 * System.out.println(temp.getNota()); } }
		 * System.out.println("Media este :" + (double) (suma / counter));
		 */
		System.out.println();

	}
}
